import numpy as np
import pandas as pd
import statsmodels.api as sm
import matplotlib.pyplot as plt
pd.set_option("display.max_columns",500)

def newHpFilter(path):
    initExcel = pd.read_excel(path, sheet_name='initial', header=None)
    # 数值部分：[5:,1:]
    initData = initExcel.loc[5:, 1:]  # initData的行标是和initExcel一样的，但是列标重新从1到76编号
    hpT = pd.DataFrame()  # 用来装载hp滤波后的dataframe
    subMat=pd.DataFrame() #装cycle的
    notNa = ~initData.isna()
    for col in range(1, initData.shape[1] + 1):
        cycle, trend = sm.tsa.filters.hpfilter(initData.loc[notNa.loc[:, col], col],lamb=1600)
        subMat=pd.concat([subMat,cycle],axis=1)
        hpT = pd.concat([hpT, trend], axis=1)
    #return subMat #行标：5:76 列标：1：2159
    stdMat=subMat.std(ddof=0,axis=0)
    newSubMat=subMat.copy(deep=True)
    for ncol in range(1,subMat.shape[1]+1):
        for nrow in range(5,subMat.shape[0]+5):
            if subMat.loc[nrow,ncol]>1.5*np.abs(stdMat.loc[ncol]):
                newSubMat.loc[nrow, ncol] = 1.5 * np.abs(stdMat.loc[ncol])
            elif subMat.loc[nrow,ncol]<-1.5*np.abs(stdMat.loc[1]):
                newSubMat.loc[nrow, ncol] = -1.5 * np.abs(stdMat.loc[ncol])

    # newSubMat.to_excel(excel_writer='D:\\subMat.xlsx')
    # hpT.to_excel(excel_writer='D:\\hpT.xlsx')
    resMat=newSubMat+hpT #resMat 任然是index:[5,76]  columns:[1,2159]
    fAns=pd.DataFrame()
    for rCol in range(1, initData.shape[1] + 1):
        Cycle1, Trend1 = sm.tsa.filters.hpfilter(resMat.loc[notNa.loc[:, rCol], rCol], 10)
        fAns=pd.concat([fAns,Trend1],axis=1)
    #fAns.to_excel(excel_writer='D:\\shuju.xlsx',sheet_name='sheet1',header=None,index=False)
    initExcel.loc[5:, 1:] = fAns
    # 交换第0行和第4行位置，让X1,X2,X3...这一行变成第一行
    a, b = initExcel.loc[0, :].copy(deep=True), initExcel.loc[4, :].copy(deep=True)
    initExcel.loc[0, :], initExcel.loc[4, :] = b, a
    # 处理日期格式，不然写入excel后日期就变成了#######
    initExcel.loc[5:, 0] = initExcel.loc[5:, 0].apply(lambda x: str(pd.to_datetime(x).date()))
    # 指定header=None，Index=False，不然会将行、列的索引号也写入excel中
    initExcel.to_excel(excel_writer='D:\\in新的python跑出来hp滤波.xlsx', sheet_name='Sheet1', header=None, index=False)
    return initExcel
if __name__ == '__main__':
    path='D:\\2020data01.xlsx'
    ans=newHpFilter(path)
    print(ans)